package com.example.hotbar;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class HotBarDB {
	
	public static final String KEY_ROWID ="row_id";
	public static final String BAR_NAME = "bar_name";
	public static final String LOCATION = "bar_location";
	public static final String BAR_POPULATION = "bar_population";
	
	private static final String DATABASE_NAME = "HotSpotDb3";
	private static final String DATABASE_TABLE = "BarTable";
	private static final int DATABASE_VERSION= 2;
	
	private DbHelper ourHelper;
	private final Context ourContext;
	private SQLiteDatabase ourDatabase;
	
	private static class DbHelper extends SQLiteOpenHelper {

		public DbHelper(Context context) {
			
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			db.execSQL(" CREATE TABLE " + DATABASE_TABLE + " ( " + 
			KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
			BAR_NAME + " TEXT NOT NULL, " +
			LOCATION + " TEXT NOT NULL, " +
			BAR_POPULATION + " TEXT  );");
			
			//db.execSQL(" CREATE TABLE DATABASE_TABLE "+ "( KEY_ROWID  INTEGER PRIMARY KEY AUTOINCREMENT , " + "BAR_NAME TEXT NOT NULL ,"  + "LOCATION TEXT NOT NULL);"); 
					
					
					
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
			onCreate(db);
		}
		
	}
	 
	public HotBarDB(Context c){
		ourContext = c;
	}
	
	public HotBarDB open() throws SQLException{
		ourHelper = new DbHelper(ourContext);
		ourDatabase = ourHelper.getWritableDatabase();
		return this;
	}
	public void close(){
		ourHelper.close();
	}
	
	public long createEntry (String name, String location){
		ContentValues cv = new ContentValues();
		cv.put(BAR_NAME, name);
		cv.put(LOCATION, location);
		
		return ourDatabase.insert(DATABASE_TABLE, null, cv);
		
	}

	public String getData() {
		// TODO Auto-generated method stub
		String[] columns = new String[] {KEY_ROWID, BAR_NAME, LOCATION, BAR_POPULATION };
		Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null , null, null);
		String result = "";
		
		int iRow = c.getColumnIndex(KEY_ROWID);
		int iName = c.getColumnIndex(BAR_NAME);
		int iLocation = c.getColumnIndex(LOCATION);
		int iPopulation = c.getColumnIndex(BAR_POPULATION);
		
		for(c.moveToFirst(); ! c.isAfterLast(); c.moveToNext()){
			result = result + c.getString(iRow) + " " + c.getString(iName) + "            " + c.getString(iLocation) + "           " + c.getString(iPopulation) + "\n";
		}
		
		return result;
	}

	public boolean checkExistingData(String name, String location) throws SQLException {
		// TODO Auto-generated method stub
		int count = 0;
		
		String query = " SELECT * from " + DATABASE_TABLE + " WHERE " + BAR_NAME + " = ? AND " + LOCATION + "= ?";
		Cursor c = ourDatabase.rawQuery(query, new String[] {name,location});
		 if (c.getCount() == count)
		 {
			 return true;
		 }
		 else 
			 return false;
		
	}

	public String getAllBarsInCity(String location ) {
		// TODO Auto-generated method stub
		
		Cursor c = ourDatabase.rawQuery(" SELECT * FROM " + DATABASE_TABLE + " WHERE " + LOCATION + " = ?;", new String[] {location});
		int iRow = c.getColumnIndex(KEY_ROWID);
		int iName = c.getColumnIndex(BAR_NAME);
		int iLocation = c.getColumnIndex(LOCATION);
		int iPopulation = c.getColumnIndex(BAR_POPULATION);
		String result = "";
		for(c.moveToFirst(); ! c.isAfterLast(); c.moveToNext()){
			result = result +  c.getString(iRow) + " " + c.getString(iName) + "            " + c.getString(iLocation) + "  " + c.getString(iPopulation) + "\n";
		}
		
		return result;
	}

	public String getBarName(String etBarLocationSearch) {
		// TODO Auto-generated method stub
		String[] columns = new String[] {KEY_ROWID, BAR_NAME, LOCATION, BAR_POPULATION};
		Cursor c = ourDatabase.query(DATABASE_TABLE, columns, BAR_NAME + "=" + etBarLocationSearch  , null, null, null, null, null);
		if (c != null){
			c.moveToFirst();
			String name = c.getString(1);
			return name;
		}
		return null;
	}

	public String getLocation(String etBarLocationSearch) {
		// TODO Auto-generated method stub
		String[] columns = new String[] {KEY_ROWID, BAR_NAME, LOCATION, BAR_POPULATION};
		Cursor c = ourDatabase.query(DATABASE_TABLE, columns, BAR_NAME + "=" + etBarLocationSearch  , null, null, null, null, null);
		if (c != null){
			c.moveToFirst();
			String location = c.getString(2);
			return location;
		}
		return null;
	}

	public void updateBarCountDB(String barName, String barLocation, String longCount) {
		// TODO Auto-generated method stub
		
		ContentValues cvUpdate = new ContentValues();
		//cvUpdate.put(BAR_NAME, barName);
		//cvUpdate.put(LOCATION, barLocation);
		cvUpdate.put(BAR_POPULATION, longCount);
		ourDatabase.update(DATABASE_TABLE, cvUpdate, BAR_NAME + " = '" + barName + "' AND " + LOCATION + " = '" + barLocation + "'", null);
	}
	
}
